package com.zhu.gmallpublisher.mapper;

import com.zhu.gmallpublisher.bean.TrafficDurPerSession;
import com.zhu.gmallpublisher.bean.TrafficPvPerSession;
import com.zhu.gmallpublisher.bean.TrafficSvCt;
import com.zhu.gmallpublisher.bean.TrafficUjRate;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


public interface TrafficChannelStatsMapper {


    /**
     * 根据渠道过获取当日的日活   提取相关数据 List[Map<(ch->AppStore,uv->864,uj->2),Map<>]
     * @param date  查询日期
     * @return
     */
    @Select("select ch,sum(uv_ct) as uv,sum(uj_ct) as uj from dws_traffic_vc_ch_ar_is_new_page_view_window " +
            "where toYYYYMMDD(stt) = #{date} group by ch ")
    List<Map> selectUvByCh(@Param("date") int date);


    //各渠道会话数
    @Select("select ch,sum(sv_ct) as sv from dws_traffic_vc_ch_ar_is_new_page_view_window where toYYYYMMDD(stt) = #{date} group by ch")
    List<TrafficSvCt> selectSvCt(@Param("date") Integer date);

    //各渠道会话平均浏览页面数
    @Select("select ch, sum(pv_ct) / sum(sv_ct) as pv_per from dws_traffic_vc_ch_ar_is_new_page_view_window where toYYYYMMDD(stt) = #{date} group by ch")
    List<TrafficPvPerSession> selectPvPerSession(@Param("date") Integer date);

    //各渠道会话平均访问时长
    @Select("select ch, sum(dur_sum) / sum(sv_ct) as dur_per from dws_traffic_vc_ch_ar_is_new_page_view_window where toYYYYMMDD(stt) = #{date} group by ch")
    List<TrafficDurPerSession> selectDurPerSession(@Param("date") Integer selectDate);  //ms

    // 各渠道跳出率
    @Select("select ch, sum(uj_ct) / sum(sv_ct) as uj_rate from dws_traffic_vc_ch_ar_is_new_page_view_window where toYYYYMMDD(stt) = #{date} group by ch")
    List<TrafficUjRate> selectUjRateCount(@Param("date") Integer selectDate);

}
